<?php

namespace app\admin\controller;

use app\admin\controller\common\Admin;
use app\admin\controller\common\Punav;
use app\admin\controller\common\Yan_zheng;
use think\Db;
use think\Request;
use think\Validate;
use PHPExcel;
use PHPExcel_IOFactory;

class Index extends Admin
{
    /*
*处理Excel导出
*@param $datas array 设置表格数据
*@param $titlename string 设置head
*@param $title string 设置表头
*/

    //导出测试
    public function ex()
    {

        $dir = dirname(__FILE__);//当前路径
        $phpExcel = new PHPExcel();
        $objSheet = $phpExcel->getActiveSheet();//获取当前活动的sheet操作，就是获取当前可以写入内容的页面
        $objSheet->setTitle('demo');//给当前活动sheet取名字
        $objSheet->getDefaultColumnDimension()->setWidth(16);/*默认宽度*/
        //$objSheet->getColumnDimension('k')->setWidth(50);/*设置单列宽度*/
        //添加数据
        $title1 = "实施年份，项目类型，建设类型，项目学校，项目名称，面积，中标数量，决算金额，拨出金额，剩余金额，资金规划，中央，自治区，市级，旗县，合计费用，已付费用，未付费用";
        $title1 = explode('，', $title1);
        $data = [$title1];
        //读取数据库
            $wh['data.id'] = ['>', 0];
            if (isset($_GET['date']) && !empty($_GET['date'])) {
                $wh['data.date'] = ['=', $_GET['date']];
            }
            if (isset($_GET['school']) && !empty($_GET['school'])) {
                $wh['data.school'] = ['like', "%{$_GET['school']}%"];
            }
            if (isset($_GET['name']) && !empty($_GET['name'])) {
                $wh['data.name'] = ['like', "%{$_GET['name']}%"];
            }
            //条件id
            if (isset($_GET['id']) && !empty($_GET['id'])) {
                $wh['data.school'] = ['=', $_GET['id']];
            }

              /* echo $sql;*/
              #获取项目
              $sql = Db::table('q_datalist')
                  ->alias('data')
                  ->where($wh)
                  ->order('data.id desc')
                  ->select();
        for ($i = 0; $i <count($sql); $i++) {
            $sql[$i]['bo_chu_money'] = $this ->bo_chu($sql[$i]['id']);//拨出合计
            $sql[$i]['shou_xu_ying_zhi_fu'] = $this ->shou_xu($sql[$i]['id']);//手续应支付合计
            $sql[$i]['shou_xu_yi_zhi_fu'] = $this ->yi_zhi_fu($sql[$i]['id']);//手续已支付合计
        }
        /*var_dump($sql);
                return;*/
        foreach ($sql as $index => $key){
            switch ($key['type']){
                case 0:
                    $key['type'] = '新建';
                    break;
                case 1:
                    $key['type'] = '扩建';
                    break;
                case 2:
                    $key['type'] = '维修';
                    break;
                case 3:
                    $key['type'] = '改建';
                    break;
                default:
                    $key['type'] = '设备';
            }
            $data[$index+1][] =$key['date'];
            $data[$index+1][] =$key['category'] == 0?'土建':'设备';
            $data[$index+1][] = $key['type'];
            $data[$index+1][] =$key['school'];
            $data[$index+1][] =$key['name'];
            $data[$index+1][] =$key['count'];
            $data[$index+1][] =$key['budgetary_price'];
            $data[$index+1][] =$key['determine_mobile'];
            $data[$index+1][] =$key['bo_chu_money'];/*拨出金额*/
            $data[$index+1][] =$key['determine_mobile'] - $key['bo_chu_money'];
            $data[$index+1][] = $key['money'];
            $data[$index+1][] = $key['address1'];
            $data[$index+1][] = $key['address2'];
            $data[$index+1][] = $key['address3'];
            $data[$index+1][] = $key['address4'];
            $data[$index+1][] =$key['shou_xu_ying_zhi_fu'];
            $data[$index+1][] =$key['shou_xu_yi_zhi_fu'];
            $data[$index+1][] =$key['shou_xu_ying_zhi_fu'] - $key['shou_xu_yi_zhi_fu'];
        }




        $objSheet ->fromArray($data);
        $objWr = PHPExcel_IOFactory::createWriter($phpExcel, 'Excel2007');//按照指定格式生成文件('生成的对象'，'生成的类型')
        $objWr->save('php://output');
        header('Content-Disposition: attachment;filename="'.(date('Y-m-d h')).'.xls"');//告诉浏览器输出的名称
        header('Cache-Control: max-age=0');//无缓存
    }

    #传来name=>id 返回拨出金额合计
    private function bo_chu($id){
        return Db::table('q_money')->where('panen_id',$id)->sum('money');
    }
    #传来项目id 返回手续支付详细
    private function yi_zhi_fu($id){
        return Db::table('q_start')
                ->alias('sta')
                ->where('sta.parend_id',$id)
                ->join('q_appropriation app','sta.id = app.parent_id')
                ->sum('app.money');
    }

    #传来name=>id 返回手续应支付合计
    private function shou_xu($id){
        return Db::table('q_start')
            ->where('parend_id',$id)
            ->sum('money');
    }


    //退出
    public function deleteuser()
    {
        session('dsihUGJKJOIJiojf*&%$6uig12@#$sdD', null);
        if (is_null(session('dsihUGJKJOIJiojf*&%$6uig12@#$sdD'))) {
            return 1;
        }
    }

    //修改颜色颜色
    public function color()
    {
        $Yan_zheng = new Yan_zheng();
        //验证字符
        if (!$error = $Yan_zheng->is_string([$_GET['color']])) {
            return $error;
        }
        $state = Db::table('bg_admin')->where('id', $this->userId)->update(['color' => $_GET['color']]);
        $Yan_zheng->rizhi("修改后台默认个人主题,修改成{$_GET['color']},状态:{$state}");
        return $state;
    }

    #添加后台导航
    public function add_nav()
    {
        if (\request()->isAjax()) {
            #验证字符串
            $Yan_zheng = new Yan_zheng();
            $array = [$_POST['parentId'], $_POST['name'], $_POST['url'], $_POST['sortCode'], $_POST['show']];
            if (!$error = $Yan_zheng->is_string($array)) {
                return $error;
            }
            /*添加数据*/
            $da['parentId'] = $_POST['parentId'];
            $da['name'] = $_POST['name'];
            $da['url'] = $_POST['url'];
            $da['sortCode'] = $_POST['sortCode'];
            $da['show'] = $_POST['show'];
            $state = Db::table('bg_sysmenu')->insert($da);
            $Yan_zheng->rizhi("添加后台导航:状态：{$state}" . implode(',', $da));
            return $state;

        }
        #获取分类
        $vi['data'] = Db::table('q_words')->whereLike('code', '10%')->select();
        return view('', $vi);
    }

    #删除后台导航
    public function delete_nav()
    {
        #验证数字
        $Yan_zheng = new Yan_zheng();
        if (!$error = $Yan_zheng->is_string([$_POST['id']])) {
            return $error;
        }
        $state = Db::table('bg_sysmenu')->delete($_POST['id']);
        $Yan_zheng->rizhi("删除后台导航id:{$_POST['id']},状态：{$state}");
        return $state;
    }

    #修改后台导航
    public function update_nav()
    {
        #验证字符串
        $Yan_zheng = new Yan_zheng();
        if (!$error = $Yan_zheng->is_string([$_POST['name']])) {
            return $error;
        }
        #验证数字
        if (!$error = $Yan_zheng->is_string([$_POST['id']])) {
            return $error;
        }

        $da[$_POST['name']] = $_POST['val'];
        $state = Db::table('bg_sysmenu')->where('id', $_POST['id'])->update($da);
        $Yan_zheng->rizhi("修改后台导航id:{$_POST['id']},内容：{$_POST['name']}=>{$_POST['val']},修改状态：{$state}");
        return $state;
    }

    #后台导航
    public function nav()
    {
        if (\request()->isAjax()) {
            $Punav = new Punav();
            return $Punav->re_nav_list();
        }

        return view();
    }

    #添加数据字典
    public function add_dictionaries()
    {
        if (\request()->isAjax()) {
            $da['code'] = $_POST['code'];
            $da['name'] = $_POST['name'];
            $da['memo'] = $_POST['memo'];
            $da['title'] = $_POST['title'];
            $state = Db::table('q_words')->insert($da);
            #添加日志
            $Yan_zheng = new Yan_zheng();
            $Yan_zheng->rizhi("添加一个数据字典，操作状态为：{$state},内容是:" . implode(',', $da));
            return $state;
        }
        return view();
    }

    #数据字典
    public function dictionaries()
    {
        if (\request()->isAjax()) {
            return Db::table('q_words')->select();
        }
        return view();
    }

    #修改数据字典
    public function up_dictionaries()
    {
        #验证字符串
        $Yan_zheng = new Yan_zheng();
        if (!$error = $Yan_zheng->is_string([$_POST['name']])) {
            return $error;
        }
        #验证数字
        if (!$error = $Yan_zheng->is_string([$_POST['id']])) {
            return $error;
        }

        #修改内容
        $up[$_POST['name']] = $_POST['val'];
        $state = Db::table('q_words')
            ->where('id', $_POST['id'])
            ->update($up);
        $Yan_zheng->rizhi("操作状态为：{$state},修改内容是：{$_POST['name']}=>{$_POST['val']}", $_POST['id']);
        return $state;
    }

    #删除数组字典
    public function de_dictionaries()
    {
        #验证数字
        $Yan_zheng = new Yan_zheng();
        if (!$error = $Yan_zheng->is_string([$_POST['id']])) {
            return $error;
        }
        return Db::table('q_words')->where('id', $_POST['id'])->delete();
    }

    #框架页面
    public function index()
    {
        //ajax获取数据
        if (\request()->isAjax()) {
            $Punav = new Punav();
            return $Punav->re_nav_list(['show' => ['=', 0]]);
        }
        #获取导航
        $vi['url'] = Db::table('bg_userandmenu')->where('user_id', $this->userId)->select();
        $vi['url'] = json_encode($vi['url']);
        #获取个人信息
        $vi['admin'] = Db::table('bg_admin')
            ->alias('admin')
            ->where('admin.id', $this->userId)
            ->join('bg_usergroup group', 'admin.groupId = group.id')
            ->field("
            admin.name as adminname,
            admin.color as admincolor,
            group.name as groupname
            ")
            ->find();
        return view('', $vi);
    }

    #工程列表
    public function sum()
    {

        if (\request()->isAjax()) {
            $wh['data.id'] = ['>', 0];
            if (isset($_POST['date']) && !empty($_POST['date'])) {
                $wh['data.date'] = ['=', $_POST['date']];
            }
            if (isset($_POST['school']) && !empty($_POST['school'])) {
                $wh['data.school'] = ['like', "%{$_POST['school']}%"];
            }
            if (isset($_POST['name']) && !empty($_POST['name'])) {
                $wh['data.name'] = ['like', "%{$_POST['name']}%"];
            }
            //条件id
            if (isset($_POST['id']) && !empty($_POST['id'])) {
                $wh['data.school'] = ['=', $_POST['id']];
            }
            $pag = config('pag');
            $_POST['index'] = $_POST['index'] * $pag;
            $data = Db::table('q_datalist')
                ->alias('data')
                ->limit("{$_POST['index']},{$pag}")
                ->where($wh)
                ->order('data.id desc')
                ->join('q_money mo', 'data.id = mo.panen_id', 'left')
                ->group('data.id')
                ->field("
                  data.id as dataid,/*土建id*/
                  data.school as dataschool,/*学校名称*/
                  data.name as dataname,/*项目名称*/
                  data.content as datacontent,/*建设内容*/
                  data.count as datacount,/*规划面积或数量*/
                  data.file as datafile,/*资金下达文件*/
                  data.money as datamoney,/*规划资金*/
                  data.money_img as datamoney_img,/*规划资金图*/
                  data.address1 as dataaddress1,/*中央*/
                  data.address2 as dataaddress2,/*自治区*/
                  data.address3 as dataaddress3,/*市级*/
                  data.address4 as dataaddress4,/*旗县*/
                  data.approval as dataapproval,/*项目申请批复*/
                  data.category as datacategory,/*类型*/
                  data.date as datadate,/*年份*/
                  data.error as dataerror,/*拒绝理由*/
                  data.state as datastate,/*状态*/
                  data.type as datatype,/*状态*/
                  data.budgetary_price as databudgetary_price,/*中标资金*/
                  data.budgetary_price_img as databudgetary_price_img,/*中标资金图*/
                  data.determine_mobile as datadetermine_mobile,/*决算资金*/
                  data.determine_mobile_img as datadetermine_mobile_img,/*决算资金图*/
                  sum(mo.money) as momoney/*拨出资金*/
                ")
                ->select();


            $count = Db::table('q_datalist')
                ->alias('data')
                ->where($wh)
                ->count();
            //分组
            $Punav = new Punav();
            $data = [$data, $count];
            return $data;
        }
        $data = Db::table('bg_admin')
            ->alias('admin')
            ->where('admin.id', $this->userId)
            ->join('bg_usergroup group', 'admin.groupId = group.id')
            ->value('group.name');
        $data = $data == '主管单位' ? '' : $data;
        /*if($data == '总部管理员'){
            $data = Db::table('q_datalist')->where('school',$data)->value('id');
        }
        else{
            $data='';
        }*/
        $vi['id'] = $data;
        return view('', $vi);
    }

}
